/**
 *  Copyright 2010 ShenYang voole Co. Ltd.
 *  All right reserved. 
 */
package com.wm.dao.hibernate;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.wm.bean.Area;
import com.wm.bean.City;
import com.wm.bean.Scriptorium;
import com.wm.comm.Constant;
import com.wm.dao.AreaDAO;
import com.wm.dao.CityDAO;
import com.wm.dao.ScriptoriumDAO;
import com.wm.service.web.PageList;
import com.wm.service.web.Pages;

/**
 * 功能描述:写字楼数据操作
 *
 * 创建日期 Dec 29, 2012
 *
 * @author zhangbaochao
 *
 * @version 1.0
 *
 */
public class ScriptoriumHibernateDAO extends HibernateDaoSupport implements ScriptoriumDAO{
	
	private static final String LOAD_BY_ID = "from Scriptorium where id = ?";
	private static final String REMOVE_BY_ID = "delete Scriptorium Admin where id = ?";
	private static final String LOAD_BY_SCR_NAME = "from Scriptorium where scrName link %'?'%";
	
	/**
	 * 保存Scriptorium对象
	 * 
	 * @param scriptorium
	 * 			Scriptorium
	 * @return Scriptorium
	 * 			Scriptorium
	 */
	public Scriptorium saveScriptorium(Scriptorium scriptorium){
		this.getHibernateTemplate().save(scriptorium);
		return scriptorium;
	}
	
	/**
	 * 更新Scriptorium对象
	 * 
	 * @param scriptorium
	 * 			Scriptorium
	 * @return Scriptorium
	 * 			Scriptorium
	 */
	public Scriptorium updateScriptorium(Scriptorium scriptorium){
		this.getHibernateTemplate().update(scriptorium);
		return scriptorium;
	}
	
	/**
	 * 根据ID删除Scriptorium对象
	 * 
	 * @param id
	 * 			long
	 */
	public void removeScriptoriumById(final long id){
		getHibernateTemplate().execute(new HibernateCallback() {
	        public Object doInHibernate(Session s) throws HibernateException, SQLException {
	          Query query = s.createQuery(REMOVE_BY_ID);
	          query.setLong(0, id);
	          query.executeUpdate();
	          return null;
	        }
	      });
	}
	
	/**
	 * 根据ID获取Scriptorium对象
	 * 
	 * @param id
	 * 			long
	 * @return Scriptorium
	 * 			Scriptorium
	 */
	public Scriptorium findScriptoriumById(long id){
		List l = this.getHibernateTemplate().find(LOAD_BY_ID,id);
		if(l == null || l.isEmpty()){
			return null;
		}else{
			return (Scriptorium) l.get(0);
		}
	}
	
	/**
	 * 根据参数获取Scriptorium对象列表
	 * 
	 * @param scrPinyin
	 * 			String
	 * @param isShow
	 * 			int
	 * @param orderby
	 * @param ascOrDesc
	 * @return List
	 * 			List
	 */
	public List findScriptoriumsByScrPinyinYinIsShow(String scrPinyin,int isShow, String orderby, int ascOrDesc){
		StringBuffer sb = new StringBuffer();
		sb.append("from Scriptorium where 1=1");
		if(StringUtils.isNotBlank(scrPinyin)){
			sb.append(" and(");
			sb.append(" scrAnglic like '%");
			sb.append(scrPinyin);
			sb.append("%'");
			sb.append(" or scrPhonetic like '%");
			sb.append(scrPinyin);
			sb.append("%'");
			sb.append(")");
	    }
		if (isShow != -1) {
			sb.append(" and isShow=");
			sb.append(isShow);
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				List list = query.list();
				return list;
			}
		});
	}
	
	/**
	 * 根据参数获取Scriptorium对象列表
	 * 
	 * @param cityId
	 * 			long
	 * @param isShow
	 * 			int
	 * @param orderby
	 * @param ascOrDesc
	 * @return List
	 * 			List
	 */
	public List findScriptoriumsByCityIdIsShow(long cityId,int isShow, String orderby, int ascOrDesc){
		StringBuffer sb = new StringBuffer();
		sb.append("from Scriptorium where 1=1");
		if (cityId != -1) {
			sb.append(" and cityId=");
			sb.append(cityId);
		}
		if (isShow != -1) {
			sb.append(" and isShow=");
			sb.append(isShow);
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				List list = query.list();
				return list;
			}
		});
	}
	
	/**
	 * 根据参数获取Scriptorium对象列表
	 * 
	 * @param areaId
	 * 			long
	 * @param isShow
	 * 			int
	 * @param orderby
	 * @param ascOrDesc
	 * @return List
	 * 			List
	 */
	public List findScriptoriumsByAreaIdIsShow(long areaId,int isShow, String orderby, int ascOrDesc){
		StringBuffer sb = new StringBuffer();
		sb.append("from Scriptorium where 1=1");
		if (areaId != -1) {
			sb.append(" and areaId=");
			sb.append(areaId);
		}
		if (isShow != -1) {
			sb.append(" and isShow=");
			sb.append(isShow);
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				List list = query.list();
				return list;
			}
		});
	}
	
	/**
	 * 根据参数获取Scriptorium对象列表
	 * 
	 * @param cityId
	 * 			long
	 * @param areaId
	 * 			long
	 * @param isShow
	 * 			int
	 * @param orderby
	 * @param ascOrDesc
	 * @param firstResult
	 * @param maxResults
	 * @return List
	 * 			List
	 */
	public List findScriptoriumsByScrNameCityIdAreaIdIsShow(String scrName,long cityId,long areaId,int isShow,String orderby,int ascOrDesc,final int firstResult,final int maxResults){
		StringBuffer sb = new StringBuffer();
		sb.append("from Scriptorium where 1=1");
		if(StringUtils.isNotBlank(scrName)){
			sb.append(" and (scrName like '%");
			sb.append(scrName.trim());
			sb.append("%'");
			sb.append(" or scrPhonetic like '%");
			sb.append(scrName.trim());
			sb.append("%'");
			sb.append(" or scrAnglic like '%");
			sb.append(scrName.trim());
			sb.append("%')");
		}
		if (cityId != -1) {
			sb.append(" and cityId=");
			sb.append(cityId);
		}
		if (areaId != -1) {
			sb.append(" and areaId=");
			sb.append(areaId);
		}
		if (isShow != -1) {
			sb.append(" and isShow=");
			sb.append(isShow);
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				query.setFirstResult(firstResult);
				query.setMaxResults(maxResults);
				List list = query.list();
				return list;
			}
		});
	}
	
	/**
	 * 根据参数获取Scriptorium对象个数
	 * 
	 * @param cityId
	 * 			long
	 * @param areaId
	 * 			long
	 * @param isShow
	 * 			int
	 * @return long
	 * 			long
	 */
	public long getScriptoriumNumByScrNameCityIdAreaIdIsShow(String scrName,long cityId,long areaId,int isShow){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(id) from Scriptorium where 1=1");
		if(StringUtils.isNotBlank(scrName)){
			sb.append(" and (scrName like '%");
			sb.append(scrName.trim());
			sb.append("%'");
			sb.append(" or scrPhonetic like '%");
			sb.append(scrName.trim());
			sb.append("%'");
			sb.append(" or scrAnglic like '%");
			sb.append(scrName.trim());
			sb.append("%')");
		}
		if (cityId != -1) {
			sb.append(" and cityId=");
			sb.append(cityId);
		}
		if (areaId != -1) {
			sb.append(" and areaId=");
			sb.append(areaId);
		}
		if (isShow != -1) {
			sb.append(" and isShow=");
			sb.append(isShow);
		}
		List l = this.getHibernateTemplate().find(sb.toString());
		
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return (Long) l.get(0);
		}
	}
	
	/**
	 * 根据参数获取Scriptorium对象列表
	 * 
	 * @param isShow
	 * 			int
	 * @param orderby
	 * @param ascOrDesc
	 * @param firstResult
	 * @param maxResults
	 * @return List
	 * 			List
	 */
	public List findScriptoriumsByIsShow(int isShow,String orderby,int ascOrDesc,final int firstResult,final int maxResults){
		StringBuffer sb = new StringBuffer();
		sb.append("from Scriptorium where 1=1");
		if (isShow != -1) {
			sb.append(" and isShow=");
			sb.append(isShow);
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				query.setFirstResult(firstResult);
				query.setMaxResults(maxResults);
				List list = query.list();
				return list;
			}
		});
	}
	
	/**
	 * 根据参数获取Scriptorium对象个数
	 * 
	 * @param isShow
	 * 			int
	 * @return long
	 * 			long
	 */
	public long getScriptoriumNumByIsShow(int isShow){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(id) from Scriptorium where 1=1");
		if (isShow != -1) {
			sb.append(" and isShow=");
			sb.append(isShow);
		}
		List l = this.getHibernateTemplate().find(sb.toString());
		
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return (Long) l.get(0);
		}
	}
	
	/**
	 * 根据参数获取Scriptorium对象列表
	 */
	public List findScriptoriumsByScrInitialsScrAnglicScrPhoneticScrTypeAreaIdIsShow(String scrInitials,String scrAnglic,String scrPhonetic,int scrType,long areaId,int isShow,String orderby,int ascOrDesc){
		StringBuffer sb = new StringBuffer();
		sb.append("from Scriptorium where 1=1");
		if (scrInitials != null) {
			sb.append(" and scrInitials='"+scrInitials+"'");
		}
		if (scrInitials != null) {
			sb.append(" and scrAnglic='"+scrAnglic+"'");
		}
		if (scrInitials != null) {
			sb.append(" and scrPhonetic='"+scrPhonetic+"'");
		}
		if (scrType != -1) {
			sb.append(" and scrType=");
			sb.append(scrType);
		}
		if (areaId != -1) {
			sb.append(" and areaId=");
			sb.append(areaId);
		}
		if (isShow != -1) {
			sb.append(" and isShow=");
			sb.append(isShow);
		}
		if (StringUtils.isNotBlank(orderby)) {
			if (ascOrDesc == Constant.ORDER_ASC) {
				sb.append(" order by " + orderby+" asc");
			}
			if (ascOrDesc == Constant.ORDER_DESC) {
				sb.append(" order by " + orderby+" desc");
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				List list = query.list();
				return list;
			}
		});
	}
	
	/**
	 * 根据参数获取Scriptorium对象列表
	 * @param scrName
	 * @return
	 */
	public List findScriptoriumByScrName(String scrName){
		return this.getHibernateTemplate().find(LOAD_BY_SCR_NAME,scrName);
	}
}
